package com.zw.blog.dao;

import com.zw.blog.po.Blog;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface BlogRepository extends JpaRepository<Blog, Long>, JpaSpecificationExecutor<Blog> {

    Blog findBlogById(Long id);

    @Query("select b from Blog b where b.published = true and b.enableRecommend = true")
    Page<Blog> findAllPublishedNotPrivate(Pageable pageable);

    @Query("select b from Blog b where b.published = true")
    Page<Blog> findAllPublished(Pageable pageable);

    // JPQL自定义查询语句
    @Query("select b from Blog b where b.enableRecommend = true and b.published = true")
    List<Blog> findTop(Pageable pageable);

    // 1代表query
    // select * from t_blog where title like '%内容%'
    @Query("select b from Blog b where b.published = true and (b.title like ?1 or b.content like ?1)")
    Page<Blog> findByQuery(String query, Pageable pageable);

    @Query("select b from Blog b where b.published = true and b.enableRecommend = true and (b.title like ?1 or b.content like ?1)")
    Page<Blog> findByQueryNotPrivate(String query, Pageable pageable);

    // 选取所有的年份
    @Query("select function('date_format', b.updateTime, '%Y') as year from Blog b group by function('date_format', b.updateTime, '%Y') order by function('date_format', b.updateTime, '%Y') desc")
    List<String> findGroupYear();

    @Query("select b from Blog b where function('date_format', b.updateTime, '%Y') = ?1 and b.published = true order by b.updateTime desc")
//    @Query("select b from Blog b where function('date_format', b.updateTime, '%Y') = ?1 and b.published = true order by function('date_format', b.updateTime, '%m-%D') desc") 这样不行 没用 不知道为什么
    List<Blog> findByYear(String year);

    @Query("select b from Blog b where function('date_format', b.updateTime, '%Y') = ?1 and b.published = true and b.enableRecommend = true order by b.updateTime desc")
    List<Blog> findByYearNotPrivate(String year);
}
